﻿--HEADER  Update Stored Procedure

-- Изменить процедуру "DataConsistency.DetectInvalidIdeaData"
GO
ALTER PROCEDURE DataConsistency.DetectInvalidIdeaData @FundBookEntityId int = NULL, @IdeaName nvarchar(150) = NULL, @IdeaId uniqueidentifier = NULL
AS

WITH minTradeDates AS
(
	SELECT MIN(t.[Date]) AS Date, InvestmentId, t.SecurityId 
	FROM Analytics.TradedQuantity tq
	INNER JOIN TradingData.Trade t ON tq.TradeId = t.Id	
	GROUP BY InvestmentId, t.SecurityId
),
maxTradeDates AS
(
	SELECT MAX(t.[Date]) AS Date, InvestmentId, t.SecurityId 
	FROM Analytics.TradedQuantity tq
	INNER JOIN TradingData.Trade t ON tq.TradeId = t.Id	
	GROUP BY InvestmentId, t.SecurityId
)

SELECT * FROM
(
	SELECT idea.FundBookEntityId, idea.Id AS IdeaId, idea.Name AS IdeaName, inv.Id AS InvestmentId, t.SecurityId, 
		CONVERT(varchar(30),minTD.[Date],126) AS FirstTradeDate, CONVERT(varchar(30),maxTD.[Date],126) AS LastTradeDate,
		NoTimeSeries = CASE WHEN
							(								
								SELECT COUNT(*)
								FROM
								(
									SELECT 
										[Date], [Close], [MarketCap], [StockReturn]
									FROM TradingData.TimeSerie ts
									WHERE SecurityId = t.SecurityId AND [Date] <= t.[Date] 
										AND [Close] IS NOT NULL
									UNION
									SELECT TOP 1
										[Date], [Close], [MarketCap], [StockReturn]
									FROM TradingData.TimeSerie
									WHERE SecurityId = t.SecurityId AND [Date] > t.[Date]
										AND [Date] <= (SELECT MAX([Date])
														FROM
														(
															SELECT TOP 4 [Date] -- using 4 days window
															FROM TradingData.TimeSerie
															WHERE SecurityId = t.SecurityId AND [Date] > t.[Date]
																AND [Close] IS NOT NULL
															ORDER BY [Date]
														)s)
										AND [Close] IS NOT NULL                                
									ORDER BY [Date]
								) s
							) > 0 THEN 'false' ELSE 'true' END,
		NoFundBookSeries = CASE WHEN 
							(
								SELECT COUNT(*) 
								FROM TradingData.FundBookEntitySerie
								WHERE FundBookEntityId = idea.FundBookEntityId AND [Date] <= t.[Date]
									AND OpenCapital IS NOT NULL AND OpenCapital > 0
							) > 0 THEN 'false' ELSE 'true' END,
		NoBenchmarkSeries = CASE WHEN 
							(
								SELECT COUNT(*)
								FROM TradingData.TimeSerie ts
								WHERE SecurityId = bsb.BenchmarkId AND [Date] <= t.[Date] 
									AND [Close] IS NOT NULL		
							) > 0 THEN 'false' ELSE 'true' END,
		NoFxRates = CASE WHEN
							(
								SELECT COUNT(*)
								FROM TradingData.FxSerie fxFund, 
									 TradingData.FxSerie fxSecurity 
								WHERE fxSecurity.CurrencyId = s.CurrencyId AND fxFund.CurrencyId = fe.CurrencyId 
									 AND fxFund.[Date] = fxSecurity.[Date] AND fxFund.[Date] <= t.[Date]
							) > 0 THEN 'false' ELSE 'true' END,
		NoExcessTimeSeries = CASE WHEN
							(
								SELECT COUNT(*)
								FROM TradingData.ExcessTimeSerie ets
								WHERE SecurityId = bsb.SecurityId AND BenchmarkId = bsb.BenchmarkId AND fe.Id = bsb.FundBookEntityId AND [Date] <= t.[Date] 
							) > 0 THEN 'false' ELSE 'true' END
	FROM Analytics.Idea idea
	inner join Analytics.Investment inv ON idea.Id = inv.IdeaId
	inner join Analytics.TradedQuantity tq ON inv.Id = tq.InvestmentId
	inner join TradingData.Trade t ON tq.TradeId = t.Id
	inner join minTradeDates minTD ON minTD.InvestmentId = inv.Id AND minTD.SecurityId = t.SecurityId AND minTD.[Date] = t.[Date]
	inner join maxTradeDates maxTD ON maxTD.InvestmentId = inv.Id AND maxTD.SecurityId = t.SecurityId
	inner join TradingData.BookSecurityToBenchmark bsb ON bsb.FundBookEntityId = idea.FundBookEntityId AND bsb.SecurityId = t.SecurityId
	inner join TradingData.Security s ON t.SecurityId = s.Id	
	inner join TradingData.FundBookEntity fe ON idea.FundBookEntityId = fe.Id
	where idea.FundBookEntityId IN
			(
				SELECT DISTINCT FundBookEntityId FROM iPerform.AnalysisGroup2FundBookEntity
			)
			AND idea.FundBookEntityId = ISNULL(@FundBookEntityId, idea.FundBookEntityId)
			AND idea.Name LIKE '%' + ISNULL(@IdeaName, idea.Name) + '%' 
			AND idea.Id = ISNULL(@IdeaId, idea.Id)
) s
WHERE s.NoTimeSeries = 'true' OR s.NoFundBookSeries = 'true' OR s.NoBenchmarkSeries = 'true' OR s.NoFxRates = 'true' OR s.NoExcessTimeSeries = 'true'
ORDER BY s.FirstTradeDate